package myjdbc;

import java.sql.*;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Random;

/**
 * @Auther: cty
 * @Date: 2020/1/21 11:07
 * @Description:  测试时间类型存取
 * java.sql.Date  年月日  ————  mysql date
 * java.sql.Time  时分秒  ————  mysql time
 * java.sql.Timestamp  年月日时分秒  ————  mysql timestamp
 * @version: 1.0
 */
public class JDBC_Demo06_Date {

    public static final String URL = "jdbc:mysql://localhost:3306/testjdbc";
    public static final String USER = "root";
    public static final String PASSWORD = "123456";

    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1. 加载MySQL驱动程序
        Class.forName("com.mysql.jdbc.Driver");

        //2. 获得数据库连接
        Connection conn = DriverManager.getConnection(URL,USER,PASSWORD);

        //3. 操作数据库，实现增删改查
        //（1）插入数据
        String sql = "insert into user(username,pwd,regDate,regTime,regTimestamp,randTimestamp) values(?,?,?,?,?,?)";  //?是占位符
        PreparedStatement ps = null;
        for(int i=0; i<1000; i++){
            ps = conn.prepareStatement(sql);
            ps.setObject(1,"cty");
            ps.setObject(2,"123");
            ps.setObject(3, new java.sql.Date(System.currentTimeMillis()));  //res: 2020-01-21
            ps.setObject(4, new java.sql.Time(System.currentTimeMillis()));  //res: 11:41:30
            ps.setObject(5, new java.sql.Timestamp(System.currentTimeMillis()));  //res: 2020-01-21 11:41:30
            int rand = 1000000000+ new Random().nextInt(1000000000);
            ps.setTimestamp(6, new java.sql.Timestamp(System.currentTimeMillis()-rand));  //随机日期  res: 2020-01-20 03:28:58
            ps.execute();
        }
        System.out.println("存入1000条记录！");

        //（2）查询指定时间段数据
        String sql2 = "select id,username,randTimestamp from user where randTimestamp>? and randTimestamp<? order by randTimestamp";
        PreparedStatement psq = conn.prepareStatement(sql2);
        Timestamp start = new Timestamp(str2stamp("2020-01-06 12:00:00"));
        Timestamp end = new Timestamp(str2stamp("2020-01-06 13:00:00"));
        psq.setObject(1, start);
        psq.setObject(2, end);
        ResultSet rs = psq.executeQuery();

        //4. 处理返回数据  如果有数据，rs为更新的行数
        while (rs.next()){
            System.out.println("id:"+rs.getString("id")+
                    "————tusername:"+rs.getString("username")+
                    "————trandTimestamp:"+rs.getTimestamp("randTimestamp"));
        }

        //5. 关闭连接
        if(ps!=null){
            ps.close();
        }
        if(psq!=null){
            psq.close();
        }
        if(conn!=null){
            conn.close();
        }
    }

    /**
     * 将yy-MM-dd HH:mm:ss格式的时间字符串转化为时间戳
     * @param str
     * @return stamp
     */
    public static long str2stamp(String str){
        DateFormat df = new SimpleDateFormat("yy-MM-dd HH:mm:ss");
        java.util.Date date = null;
        try {
            date = df.parse(str);
        } catch (ParseException e) {
            e.printStackTrace();
        }
        long stamp = date.getTime();
        return stamp;
    }
}

/* res:
存入1000条记录！
id:20457————tusername:cty————trandTimestamp:2020-01-06 12:01:32.0
id:21082————tusername:cty————trandTimestamp:2020-01-06 12:06:23.0
id:21458————tusername:cty————trandTimestamp:2020-01-06 12:15:06.0
id:20122————tusername:cty————trandTimestamp:2020-01-06 12:15:19.0
id:20910————tusername:cty————trandTimestamp:2020-01-06 12:22:08.0
id:20445————tusername:cty————trandTimestamp:2020-01-06 12:37:53.0
id:20538————tusername:cty————trandTimestamp:2020-01-06 12:38:35.0
id:20825————tusername:cty————trandTimestamp:2020-01-06 12:41:53.0
id:20551————tusername:cty————trandTimestamp:2020-01-06 12:48:18.0
id:20562————tusername:cty————trandTimestamp:2020-01-06 12:48:26.0
*/